package com.arong.swing.db.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.arong.swing.db.JDBCUtils;
import com.arong.swing.db.entity.CrCarRecord;
import com.arong.swing.db.entity.PagingInfo;
import com.arong.swing.db.entity.PagingResult;
import com.arong.swing.exception.AppException;
import com.arong.swing.exception.SysException;
import com.arong.swing.util.StringUtil;
public class CrCarRecordSDAO  extends BaseDAO {
	public CrCarRecordSDAO(){
		this(DB_CHECKRECORD);
	}
	public CrCarRecordSDAO(String dbFile){
		super(dbFile);
	}
	public CrCarRecordSDAO(JDBCUtils jdbcUtils){
		super(jdbcUtils);
	}
	public String getCreateTableSql(){
		return "CREATE TABLE IF NOT EXISTS "+
				"CR_CAR_RECORD("+
						 " ID  VARCHAR2(32) PRIMARY KEY,"+
						 " CHECKEVENT_ID VARCHAR2(32),"+
						 " CP VARCHAR2(20),"+
						 " HPZL VARCHAR2(32),"+//号牌种类,2016-6-17添加
						 " ZP BLOB,"+
						 " CLSBDM VARCHAR2(20),"+
						 " FDJH VARCHAR2(20),"+
						 " CLYS VARCHAR2(10),"+
						 " CZSFHM VARCHAR2(18),"+
						 " CZXM VARCHAR2(30),"+
						 " PROVINCE VARCHAR2(32),"+
						 " CITY  VARCHAR2(32),"+
						 " COUNTY VARCHAR2(32),"+
						 " PCSJ DATE,"+
						 " ALLGENERATE_ID VARCHAR2(32) NOT NULL,"+
						 " ALLPACKAGE_VER VARCHAR2(32) ,"+
						 " SJLY VARCHAR2(50),"+
						 " CREATE_TIME DATE"+
						 ")";
		
	}
	public void createTabbleIndex(){
		try {
			jdbcUtils.execute("CREATE INDEX IF NOT EXISTS INDEX_PCSJ_CARRECORD on CR_CAR_RECORD(PCSJ)");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 插入
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int insert(CrCarRecord entity) throws SysException,AppException {
		int i=0;
		try {
		 		    if (StringUtil.isBlank(entity.getId())) {
				entity.setId(getSeqValue());
			}
		 			i= jdbcUtils.insert(entity);
		} catch (SQLException e) {
			throw new SysException("add error", e);
		}
		return i;
	}

	/**
	 * 修改
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int update(CrCarRecord entity) throws SysException,AppException {
		int i=0;
		try {
			i=jdbcUtils.update(entity, "id");
		} catch (SQLException e) {
			throw new SysException("update error",e);
		}
		return i;
	}

	/**
	 * 删除
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  int delete(CrCarRecord entity) throws SysException,AppException {
		int i = 0;
		try {
			i=jdbcUtils.execute("DELETE FROM CR_CAR_RECORD WHERE ID='"
					    +entity.getId()
		    		    +"'"
			);
		} catch (SQLException e) {
			throw new SysException("delete error", e);
		}
		return i;
	}

	/**
	 * 分页查询
	 * @param sql
	 * @param pagingInfo
	 * @param entity
	 * @return 
	 * @throws SysException, AppException
	 */
	public  PagingResult<CrCarRecord> queryList(PagingInfo pagingInfo, CrCarRecord entity)
			throws SysException,AppException {
		PagingResult<CrCarRecord> res=null;
	    try {
			res= jdbcUtils.queryList(getSql(entity), CrCarRecord.class,
				pagingInfo, getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryList分页查询 error",e);
		}
		return   res;
	}

	/**
	 * 查询 集合
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public   List<CrCarRecord> queryList(CrCarRecord entity) throws SysException,AppException {
	    List<CrCarRecord> res=null;
	    try {
			res= jdbcUtils.queryList(getSql(entity), CrCarRecord.class,
			getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryList集合查询 error",e);
		}
		return   res;
	}

	/**
	 * 查询对象
	 * @param entity
	 * @return
	 * @throws SysException, AppException
	 */
	public  CrCarRecord queryBean(CrCarRecord entity) throws SysException,AppException {
		CrCarRecord res=null;
	    try {
			res=  jdbcUtils.queryBean(getSql(entity), CrCarRecord.class,
				getParams(entity));
		} catch (SQLException e) {
			throw new SysException("queryBean对象查询 error",e);
		}
		return   res;
	}

	/**
	 * 获取查询条件
	 * @return
	 */
	private Object[] getParams(CrCarRecord crCarRecord) {
		List<Object> qryList = new ArrayList<Object>();
		if (crCarRecord.getId() != null) {
			qryList.add(crCarRecord.getId());
		}
		if (crCarRecord.getCheckeventId() != null) {
			qryList.add(crCarRecord.getCheckeventId());
		}
		if (crCarRecord.getCp() != null) {
			qryList.add(crCarRecord.getCp());
		}
		if (crCarRecord.getClsbdm() != null) {
			qryList.add(crCarRecord.getClsbdm());
		}
		if (crCarRecord.getFdjh() != null) {
			qryList.add(crCarRecord.getFdjh());
		}
		if (crCarRecord.getClys() != null) {
			qryList.add(crCarRecord.getClys());
		}
		if (crCarRecord.getCzsfhm() != null) {
			qryList.add(crCarRecord.getCzsfhm());
		}
		if (crCarRecord.getCzxm() != null) {
			qryList.add(crCarRecord.getCzxm());
		}
		if (crCarRecord.getProvince() != null) {
			qryList.add(crCarRecord.getProvince());
		}
		if (crCarRecord.getCity() != null) {
			qryList.add(crCarRecord.getCity());
		}
		if (crCarRecord.getCounty() != null) {
			qryList.add(crCarRecord.getCounty());
		}
		if (crCarRecord.getPcsj() != null) {
			qryList.add(crCarRecord.getPcsj());
		}
		if (crCarRecord.getSjly() != null) {
			qryList.add(crCarRecord.getSjly());
		}
		if (crCarRecord.getCreateTime() != null) {
			qryList.add(crCarRecord.getCreateTime());
		}
		return jdbcUtils.setQryList(qryList);
	}

	/**
	 * 获取sql
	 * @return
	 */
	private String getSql(CrCarRecord crCarRecord) {
		StringBuffer sql = new StringBuffer("SELECT ");
		sql.append(" a.ID,a.CHECKEVENT_ID,a.CP,a.CLSBDM,a.FDJH,a.CLYS,a.CZSFHM,a.CZXM,a.PROVINCE,a.CITY,a.COUNTY,a.PCSJ,a.SJLY,a.CREATE_TIME");
		sql.append(" from CR_CAR_RECORD a where 1=1");
		if (crCarRecord.getId() != null) {
			sql.append(" and ID=?");
		}
		if (crCarRecord.getCheckeventId() != null) {
			sql.append(" and CHECKEVENT_ID=?");
		}
		if (crCarRecord.getCp() != null) {
			sql.append(" and CP=?");
		}
		if (crCarRecord.getClsbdm() != null) {
			sql.append(" and CLSBDM=?");
		}
		if (crCarRecord.getFdjh() != null) {
			sql.append(" and FDJH=?");
		}
		if (crCarRecord.getClys() != null) {
			sql.append(" and CLYS=?");
		}
		if (crCarRecord.getCzsfhm() != null) {
			sql.append(" and CZSFHM=?");
		}
		if (crCarRecord.getCzxm() != null) {
			sql.append(" and CZXM=?");
		}
		if (crCarRecord.getProvince() != null) {
			sql.append(" and PROVINCE=?");
		}
		if (crCarRecord.getCity() != null) {
			sql.append(" and CITY=?");
		}
		if (crCarRecord.getCounty() != null) {
			sql.append(" and COUNTY=?");
		}
		if (crCarRecord.getPcsj() != null) {
			sql.append(" and PCSJ=?");
		}
		if (crCarRecord.getSjly() != null) {
			sql.append(" and SJLY=?");
		}
		if (crCarRecord.getCreateTime() != null) {
			sql.append(" and CREATE_TIME=?");
		}
		return sql.toString();
	}
}

